﻿/*
TestMiniAccessDatabase 的部署脚本
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT, QUOTED_IDENTIFIER OFF;


GO
:setvar DatabaseName "TestMiniAccessDatabase"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"

GO
:on error exit
GO
USE [master]
GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL
    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
    RAISERROR(N'目标数据库 %s 的状态未设置为 ONLINE。要部署此数据库，数据库状态必须设置为 ONLINE。', 16, 127,N'$(DatabaseName)') WITH NOWAIT
    RETURN
END

GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'正在创建 $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON 
    PRIMARY(NAME = [PrimaryFileName], FILENAME = '$(DefaultDataPath)$(DatabaseName).mdf')
    LOG ON (NAME = [PrimaryLogFileName], FILENAME = '$(DefaultDataPath)$(DatabaseName)_log.ldf') COLLATE Chinese_PRC_CI_AS
GO
EXECUTE sp_dbcmptlevel [$(DatabaseName)], 90;


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS OFF,
                ANSI_PADDING OFF,
                ANSI_WARNINGS OFF,
                ARITHABORT OFF,
                CONCAT_NULL_YIELDS_NULL OFF,
                NUMERIC_ROUNDABORT OFF,
                QUOTED_IDENTIFIER OFF,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL,
                RECOVERY FULL,
                CURSOR_CLOSE_ON_COMMIT OFF,
                AUTO_CREATE_STATISTICS ON,
                AUTO_SHRINK OFF,
                AUTO_UPDATE_STATISTICS ON,
                RECURSIVE_TRIGGERS OFF 
            WITH ROLLBACK IMMEDIATE;
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CLOSE OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ALLOW_SNAPSHOT_ISOLATION OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET READ_COMMITTED_SNAPSHOT OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_UPDATE_STATISTICS_ASYNC ON,
                PAGE_VERIFY NONE,
                DATE_CORRELATION_OPTIMIZATION OFF,
                DISABLE_BROKER,
                PARAMETERIZATION SIMPLE,
                SUPPLEMENTAL_LOGGING OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET TRUSTWORTHY OFF,
        DB_CHAINING OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'无法修改数据库设置。您必须是 SysAdmin 才能应用这些设置。';
    END


GO
USE [$(DatabaseName)]
GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'disable';


GO
/*
 预先部署脚本模板							
--------------------------------------------------------------------------------------
 此文件包含将在生成脚本前执行的 SQL 语句	
 使用 SQLCMD 语法将文件包含在预先部署脚本中			
 示例:      :r .\filename.sql								
 使用 SQLCMD 语法引用预先部署脚本中的变量		
 示例:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/




GO
PRINT N'正在创建 [dbo].[DataTypeTable]...';


GO
CREATE TABLE [dbo].[DataTypeTable] (
    [TableId]           INT              IDENTITY (1, 1) NOT NULL,
    [IntField]          INT              NULL,
    [IntEnumField]      INT              NULL,
    [NVarCharEnumField] NVARCHAR (50)    NULL,
    [BitField]          BIT              NULL,
    [DateTimeField]     DATETIME         NULL,
    [DecimalField]      DECIMAL (18, 6)  NULL,
    [FloatField]        FLOAT            NULL,
    [MoneyField]        MONEY            NULL,
    [NTextField]        NTEXT            NULL,
    [NVarCharField]     NVARCHAR (50)    NULL,
    [RealField]         REAL             NULL,
    [GuidField]         UNIQUEIDENTIFIER NULL,
    [ImageField]        IMAGE            NULL,
    [VarBinaryField]    VARBINARY (50)   NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];


GO
PRINT N'正在创建 PK_DataTypeTable...';


GO
ALTER TABLE [dbo].[DataTypeTable]
    ADD CONSTRAINT [PK_DataTypeTable] PRIMARY KEY CLUSTERED ([TableId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];


GO
PRINT N'正在创建 [dbo].[ForeignKeyTable]...';


GO
CREATE TABLE [dbo].[ForeignKeyTable] (
    [CompositeKey1] NVARCHAR (50)    NOT NULL,
    [CompositeKey2] NVARCHAR (50)    NOT NULL,
    [CompositeKey3] NVARCHAR (50)    NOT NULL,
    [PrimaryKeyId]  NVARCHAR (50)    NOT NULL,
    [TableId]       INT              NOT NULL,
    [GuidField]     UNIQUEIDENTIFIER NOT NULL,
    [Field1]        INT              NULL,
    [Field2]        INT              NULL
) ON [PRIMARY];


GO
PRINT N'正在创建 PK_ForeignKeyTable...';


GO
ALTER TABLE [dbo].[ForeignKeyTable]
    ADD CONSTRAINT [PK_ForeignKeyTable] PRIMARY KEY CLUSTERED ([CompositeKey1] ASC, [CompositeKey2] ASC, [CompositeKey3] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];


GO
PRINT N'正在创建 [dbo].[PrimaryKeyTable]...';


GO
CREATE TABLE [dbo].[PrimaryKeyTable] (
    [PrimaryKeyId] NVARCHAR (50) NOT NULL,
    [Field1]       INT           NULL,
    [Field2]       INT           NULL
) ON [PRIMARY];


GO
PRINT N'正在创建 PK_PrimaryKeyTable...';


GO
ALTER TABLE [dbo].[PrimaryKeyTable]
    ADD CONSTRAINT [PK_PrimaryKeyTable] PRIMARY KEY CLUSTERED ([PrimaryKeyId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];


GO
PRINT N'正在创建 [dbo].[Test]...';


GO
CREATE TABLE [dbo].[Test] (
    [Id]          INT             IDENTITY (1, 1) NOT NULL,
    [FirstName]   NVARCHAR (50)   NULL,
    [LastName]    NVARCHAR (50)   NULL,
    [Sex]         NVARCHAR (50)   NULL,
    [Age]         INT             NULL,
    [HeadImage]   VARBINARY (MAX) NOT NULL,
    [Description] NTEXT           NOT NULL
);


GO
PRINT N'正在创建 PK_Test...';


GO
ALTER TABLE [dbo].[Test]
    ADD CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'正在创建 [dbo].[WithoutKeyTable]...';


GO
CREATE TABLE [dbo].[WithoutKeyTable] (
    [Field1] INT NULL,
    [Field2] INT NULL,
    [Field3] INT NULL
) ON [PRIMARY];


GO
PRINT N'正在创建 FK_ForeignKeyTable_PrimaryKeyTable...';


GO
ALTER TABLE [dbo].[ForeignKeyTable] WITH NOCHECK
    ADD CONSTRAINT [FK_ForeignKeyTable_PrimaryKeyTable] FOREIGN KEY ([PrimaryKeyId]) REFERENCES [dbo].[PrimaryKeyTable] ([PrimaryKeyId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'正在创建 [dbo].[Delete_DataTypeTable]...';


GO
CREATE PROCEDURE Delete_DataTypeTable
	@TableId int
AS
DELETE FROM [DataTypeTable]
WHERE
	[TableId] = @TableId
GO
PRINT N'正在创建 [dbo].[Delete_DataTypeTable_By_Conditions]...';


GO
CREATE PROCEDURE Delete_DataTypeTable_By_Conditions
	@Conditions NVarChar(800)=null
AS
DECLARE @N NVarChar(800);
SET @N=N'DELETE
	FROM [DataTypeTable]'
	+ @Conditions;
EXECUTE sp_executesql @N
GO
PRINT N'正在创建 [dbo].[Delete_ForeignKeyTable]...';


GO
CREATE PROCEDURE Delete_ForeignKeyTable
	@CompositeKey1 nvarchar(100),
	@CompositeKey2 nvarchar(100),
	@CompositeKey3 nvarchar(100)
AS
DELETE FROM [ForeignKeyTable]
WHERE
	[CompositeKey1] = @CompositeKey1 AND
	[CompositeKey2] = @CompositeKey2 AND
	[CompositeKey3] = @CompositeKey3
GO
PRINT N'正在创建 [dbo].[Delete_ForeignKeyTable_By_Conditions]...';


GO
CREATE PROCEDURE Delete_ForeignKeyTable_By_Conditions
	@Conditions NVarChar(800)=null
AS
DECLARE @N NVarChar(800);
SET @N=N'DELETE
	FROM [ForeignKeyTable]'
	+ @Conditions;
EXECUTE sp_executesql @N
GO
PRINT N'正在创建 [dbo].[Delete_WithoutKeyTable_By_Conditions]...';


GO
CREATE PROCEDURE Delete_WithoutKeyTable_By_Conditions
	@Conditions NVarChar(800)=null
AS
DECLARE @N NVarChar(800);
SET @N=N'DELETE
	FROM [WithoutKeyTable]'
	+ @Conditions;
EXECUTE sp_executesql @N
GO
PRINT N'正在创建 [dbo].[Insert_DataTypeTable]...';


GO
CREATE PROCEDURE Insert_DataTypeTable
	@TableId int OUTPUT,
	@IntField int,
	@IntEnumField int,
	@NVarCharEnumField nvarchar(100),
	@BitField bit,
	@DateTimeField datetime,
	@DecimalField decimal(18   ,6    ),
	@FloatField float,
	@MoneyField money,
	@NTextField ntext,
	@NVarCharField nvarchar(100),
	@RealField real,
	@GuidField uniqueidentifier,
	@ImageField image,
	@VarBinaryField varbinary(50)
AS
INSERT INTO [DataTypeTable] (
	[IntField],
	[IntEnumField],
	[NVarCharEnumField],
	[BitField],
	[DateTimeField],
	[DecimalField],
	[FloatField],
	[MoneyField],
	[NTextField],
	[NVarCharField],
	[RealField],
	[GuidField],
	[ImageField],
	[VarBinaryField]
)
VALUES
(
	@IntField,
	@IntEnumField,
	@NVarCharEnumField,
	@BitField,
	@DateTimeField,
	@DecimalField,
	@FloatField,
	@MoneyField,
	@NTextField,
	@NVarCharField,
	@RealField,
	@GuidField,
	@ImageField,
	@VarBinaryField
)
SET @TableId = @@Identity
GO
PRINT N'正在创建 [dbo].[Insert_ForeignKeyTable]...';


GO
CREATE PROCEDURE Insert_ForeignKeyTable
	@CompositeKey1 nvarchar(100),
	@CompositeKey2 nvarchar(100),
	@CompositeKey3 nvarchar(100),
	@PrimaryKeyId nvarchar(100),
	@TableId int,
	@GuidField uniqueidentifier,
	@Field1 int,
	@Field2 int
AS
INSERT INTO [ForeignKeyTable] (
	[CompositeKey1],
	[CompositeKey2],
	[CompositeKey3],
	[PrimaryKeyId],
	[TableId],
	[GuidField],
	[Field1],
	[Field2]
)
VALUES
(
	@CompositeKey1,
	@CompositeKey2,
	@CompositeKey3,
	@PrimaryKeyId,
	@TableId,
	@GuidField,
	@Field1,
	@Field2
)
GO
PRINT N'正在创建 [dbo].[Insert_WithoutKeyTable]...';


GO
CREATE PROCEDURE Insert_WithoutKeyTable
	@Field1 int,
	@Field2 int,
	@Field3 int
AS
INSERT INTO [WithoutKeyTable] (
	[Field1],
	[Field2],
	[Field3]
)
VALUES
(
	@Field1,
	@Field2,
	@Field3
)
GO
PRINT N'正在创建 [dbo].[Select_DataTypeTable_By_Conditions]...';


GO
CREATE PROCEDURE Select_DataTypeTable_By_Conditions
	@Conditions NVarChar(800)=null
AS
DECLARE @N NVarChar(2000);
SET @N=N'SELECT
	[DataTypeTable].[TableId],
	[DataTypeTable].[IntField],
	[DataTypeTable].[IntEnumField],
	[DataTypeTable].[NVarCharEnumField],
	[DataTypeTable].[BitField],
	[DataTypeTable].[DateTimeField],
	[DataTypeTable].[DecimalField],
	[DataTypeTable].[FloatField],
	[DataTypeTable].[MoneyField],
	[DataTypeTable].[NTextField],
	[DataTypeTable].[NVarCharField],
	[DataTypeTable].[RealField],
	[DataTypeTable].[GuidField],
	[DataTypeTable].[ImageField],
	[DataTypeTable].[VarBinaryField]
	FROM [DataTypeTable] '+ @Conditions;
EXECUTE sp_executesql @N
GO
PRINT N'正在创建 [dbo].[Select_DataTypeTable_By_PrimaryKey]...';


GO
CREATE PROCEDURE Select_DataTypeTable_By_PrimaryKey
	@TableId int
AS
SELECT
	[DataTypeTable].[TableId],
	[DataTypeTable].[IntField],
	[DataTypeTable].[IntEnumField],
	[DataTypeTable].[NVarCharEnumField],
	[DataTypeTable].[BitField],
	[DataTypeTable].[DateTimeField],
	[DataTypeTable].[DecimalField],
	[DataTypeTable].[FloatField],
	[DataTypeTable].[MoneyField],
	[DataTypeTable].[NTextField],
	[DataTypeTable].[NVarCharField],
	[DataTypeTable].[RealField],
	[DataTypeTable].[GuidField],
	[DataTypeTable].[ImageField],
	[DataTypeTable].[VarBinaryField]
FROM [DataTypeTable]
WHERE
	[TableId] = @TableId
GO
PRINT N'正在创建 [dbo].[Select_ForeignKeyTable_By_Conditions]...';


GO
CREATE PROCEDURE Select_ForeignKeyTable_By_Conditions
	@Conditions NVarChar(800)=null
AS
DECLARE @N NVarChar(2000);
SET @N=N'SELECT
	[ForeignKeyTable].[CompositeKey1],
	[ForeignKeyTable].[CompositeKey2],
	[ForeignKeyTable].[CompositeKey3],
	[ForeignKeyTable].[PrimaryKeyId],
	[ForeignKeyTable].[TableId],
	[ForeignKeyTable].[GuidField],
	[ForeignKeyTable].[Field1],
	[ForeignKeyTable].[Field2]
	FROM [ForeignKeyTable] '+ @Conditions;
EXECUTE sp_executesql @N
GO
PRINT N'正在创建 [dbo].[Select_ForeignKeyTable_By_PrimaryKey]...';


GO
CREATE PROCEDURE Select_ForeignKeyTable_By_PrimaryKey
	@CompositeKey1 nvarchar(100),
	@CompositeKey2 nvarchar(100),
	@CompositeKey3 nvarchar(100)
AS
SELECT
	[ForeignKeyTable].[CompositeKey1],
	[ForeignKeyTable].[CompositeKey2],
	[ForeignKeyTable].[CompositeKey3],
	[ForeignKeyTable].[PrimaryKeyId],
	[ForeignKeyTable].[TableId],
	[ForeignKeyTable].[GuidField],
	[ForeignKeyTable].[Field1],
	[ForeignKeyTable].[Field2]
FROM [ForeignKeyTable]
WHERE
	[CompositeKey1] = @CompositeKey1 AND
	[CompositeKey2] = @CompositeKey2 AND
	[CompositeKey3] = @CompositeKey3
GO
PRINT N'正在创建 [dbo].[Select_WithoutKeyTable_By_Conditions]...';


GO
CREATE PROCEDURE Select_WithoutKeyTable_By_Conditions
	@Conditions NVarChar(800)=null
AS
DECLARE @N NVarChar(2000);
SET @N=N'SELECT
	[WithoutKeyTable].[Field1],
	[WithoutKeyTable].[Field2],
	[WithoutKeyTable].[Field3]
	FROM [WithoutKeyTable] '+ @Conditions;
EXECUTE sp_executesql @N
GO
PRINT N'正在创建 [dbo].[Update_DataTypeTable]...';


GO
CREATE PROCEDURE Update_DataTypeTable
	@IntField int,
	@IntEnumField int,
	@NVarCharEnumField nvarchar(100),
	@BitField bit,
	@DateTimeField datetime,
	@DecimalField decimal(18   ,6    ),
	@FloatField float,
	@MoneyField money,
	@NTextField ntext,
	@NVarCharField nvarchar(100),
	@RealField real,
	@GuidField uniqueidentifier,
	@ImageField image,
	@VarBinaryField varbinary(50),
	@TableId int
AS
UPDATE [DataTypeTable]
SET
	[IntField] = @IntField,
	[IntEnumField] = @IntEnumField,
	[NVarCharEnumField] = @NVarCharEnumField,
	[BitField] = @BitField,
	[DateTimeField] = @DateTimeField,
	[DecimalField] = @DecimalField,
	[FloatField] = @FloatField,
	[MoneyField] = @MoneyField,
	[NTextField] = @NTextField,
	[NVarCharField] = @NVarCharField,
	[RealField] = @RealField,
	[GuidField] = @GuidField,
	[ImageField] = @ImageField,
	[VarBinaryField] = @VarBinaryField
WHERE
	[TableId] = @TableId
GO
PRINT N'正在创建 [dbo].[Update_ForeignKeyTable]...';


GO
CREATE PROCEDURE Update_ForeignKeyTable
	@New_CompositeKey1 nvarchar(100),
	@New_CompositeKey2 nvarchar(100),
	@New_CompositeKey3 nvarchar(100),
	@PrimaryKeyId nvarchar(100),
	@TableId int,
	@GuidField uniqueidentifier,
	@Field1 int,
	@Field2 int,
	@CompositeKey1 nvarchar(100),
	@CompositeKey2 nvarchar(100),
	@CompositeKey3 nvarchar(100)
AS
UPDATE [ForeignKeyTable]
SET
	[CompositeKey1] = @New_CompositeKey1,
	[CompositeKey2] = @New_CompositeKey2,
	[CompositeKey3] = @New_CompositeKey3,
	[PrimaryKeyId] = @PrimaryKeyId,
	[TableId] = @TableId,
	[GuidField] = @GuidField,
	[Field1] = @Field1,
	[Field2] = @Field2
WHERE
	[CompositeKey1] = @CompositeKey1 AND
	[CompositeKey2] = @CompositeKey2 AND
	[CompositeKey3] = @CompositeKey3
GO
-- 正在重构步骤以使用已部署的事务日志更新目标服务器
CREATE TABLE  [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
GO
sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
GO

GO
/*
-- 后期部署脚本模板							
----------------------------------------------------------------------------------------
-- 此文件包含将追加到生成脚本的 SQL 语句		
-- 使用 SQLCMD 语法将文件包含到后期部署脚本中			
-- 示例:      :r .\filename.sql								
-- 使用 SQLCMD 语法引用后期部署脚本中的变量		
-- 示例:      :setvar $TableName MyTable							
--               SELECT * FROM [$(TableName)]					
----------------------------------------------------------------------------------------
*/

GRANT CONNECT TO [guest]
GO
GRANT CONNECT TO [guest]
GO




GO




GO
PRINT N'正在根据新创建的约束检查现有的数据';


GO
USE [$(DatabaseName)];


GO
ALTER TABLE [dbo].[ForeignKeyTable] WITH CHECK CHECK CONSTRAINT [FK_ForeignKeyTable_PrimaryKeyTable];


GO
